/* 
 *   Classname:  com.zzz.db.MysqlToOracle
 * 
 *   Date       Developer   Description
 *   2017年11月17日    周成功              新建
 *
 */
package demo.database;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import com.titan.core.utils.data.AssertUtils;
import com.titan.core.utils.data.FormatUtils;
import com.titan.core.utils.data.RegexUtils;
import com.titan.core.utils.data.StringUtils;
import com.titan.core.utils.file.FileUtils;

/**
 *
 *
 * @author 周成功
 *
 */
public class MysqlToOracle {
    private final static String ENCODING = "UTF-8";
    
    public static void main(String[] args) {
        try {
            String root = "D:\\我的项目\\项目-四通\\Workspace\\FordBHServer";
            
            run2(root, "base_gate");
            run2(root, "config_parameter");
            run2(root, "log_work");
            run2(root, "module");
            run2(root, "person_driver_2_gate");
            run2(root, "person_driver_group_2_gate");
            run2(root, "person_driver");
            run2(root, "person_master");
            run2(root, "person_operator_2_gate");
            run2(root, "person_operator_2_module");
            run2(root, "person_operator");
            run2(root, "vehcicle_area_lot");
            run2(root, "vehcicle_area_lot2");
            run2(root, "vehcicle_area");
            run2(root, "vehcicle_goods");
            run2(root, "vehcicle_return");
            run2(root, "vehcicle_status_2_area");
            run2(root, "vehcicle_status_2_gate");
            run2(root, "vehcicle_status");
            run2(root, "vehcicle_work");
            run2(root, "");
            run2(root, "");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    private static void run2(String root, String table) throws Exception {
        if(StringUtils.isEmpty(table)) {
            return;
        }
        run(root+"\\db-mysql\\"+table+".sql", root+"\\db-oracle\\"+table+".sql");
    }
    private static long number = Long.parseLong(FormatUtils.getSysdate("yyyyMMddHHmmss"));
    
    private static void run(String fileMysql, String fileQracle) throws Exception {
        List<String> linesMysql = FileUtils.toLines(new File(fileMysql), ENCODING);
        AssertUtils.assertNotNull(linesMysql, "输入参数fileMysql["+fileMysql+"]指定的文件不存在或者无效！");
        
        List<String> linesOracle = new ArrayList<String>();
        for (int i = 0; i < linesMysql.size(); i++) {
            String line = linesMysql.get(i);
            
            line = RegexUtils.replaceAll(line, "DROP TABLE IF EXISTS ", "DROP TABLE ");
            
            String tmp = RegexUtils.find(line, "(`)([a-z_0-9A-Z]*)(`)");
            if(StringUtils.isNotEmpty(tmp) 
                    && (line.toUpperCase().indexOf("DROP TABLE")<0) 
                    && (line.toUpperCase().indexOf("CREATE TABLE")<0) 
                    && (line.toUpperCase().indexOf("PRIMARY KEY")<0) 
                    && (line.toUpperCase().indexOf("INSERT INTO")<0) 
                    && (line.toUpperCase().indexOf("CREATE UNIQUE INDEX")<0) 
                    ) {
                line = RegexUtils.replaceAll(line, "[ \t]*COMMENT[ \t]*\\'.*\\',", ",");
                line = RegexUtils.replaceAll(line, "([ \t]*)(varchar\\()", "$1varchar2(");
                line = RegexUtils.replaceAll(line, "([ \t]*)(datetime)([ \t]*)", "$1date$3");
                line = RegexUtils.replaceAll(line, "([ \t]*)(int)([ \t]*)", "$1number(8)$3");
//                System.out.println(line);
            }
            line = RegexUtils.replaceAll(line, "(`)([a-z_0-9A-Z]*)(`)", "$2");
            line = RegexUtils.replaceAll(line, "(create[ \t]*unique[ \t]*index[ \t]*)([a-z_0-9A-Z]*)([ \t]*on)", "$1idx_"+(number++)+"$3");
            
            tmp = RegexUtils.find(line, "\\) ENGINE=InnoDB");
            if(StringUtils.isNotEmpty(tmp)) {
                line = ");";
            }
            if(line.indexOf("INSERT INTO")>=0) {
                line = "";
            }
            
            
////            tmp = RegexUtils.find(line, "create[ \t]*unique[ \t]*index[ \t]*[a-z_0-9A-Z]*[ \t]*on");
//            tmp = RegexUtils.find(line, "create[ \t]*unique[ \t]*index[ \t]*[a-z_0-9A-Z]*[ \t]*on");
//            if(StringUtils.isNotEmpty(tmp)) {
//                System.out.println(tmp);
////                System.out.println(line);
////                System.out.println(fileMysql);
//            }
            
            
            // create unique index
            
            linesOracle.add(line);
        }
        FileUtils.write(new File(fileQracle), linesOracle, ENCODING, false);
    }
    
    
    
    
}
